from django.db.models import Avg, Count, Max, Min, Sum, F, Q
from django.http import HttpResponse, HttpResponseRedirect
from django.shortcuts import render
from django.views.decorators.http import require_http_methods

from front.forms import MessageBoardForm, RegisterForm, ArticleForm
from front.models import Book, Author, Article


# Create your views here.
# 聚合函数
def avg_view(request):
    # SELECT AVG(`front_book`.`price`) AS `price__avg` FROM `front_book`
    aggregate = Book.objects.aggregate(Avg('price'))
    print(aggregate)
    return HttpResponse("avg_view")


def count_view(request):
    # SELECT COUNT(`front_book`.`id`) AS `id__count` FROM `front_book`
    aggregate = Book.objects.aggregate(Count('id'))
    print(aggregate)
    return HttpResponse("count_view")


def max_min_view(request):
    # SELECT MAX(`front_author`.`age`) AS `age__max`, MIN(`front_author`.`age`) AS `age__min` FROM `front_author`
    aggregate = Author.objects.aggregate(max_age=Max('age'), min_age=Min('age'))
    print(aggregate)
    return HttpResponse("max_min_view")


def sum_view(request):
    """
    aggregate和annotate的区别:
    1.aggregate:返回使用聚合函数后的字段和值,
    2.annotate:在原来模型字段的基础之上添加一个使用了聚合函数的字段，并且在使用聚合函数的时候，会使用当前这个模型的主键进行分组(group by)。
        比如以上 sum的例子，如果使用的是 annotate，那么将在每条图书的数据上都添加一个字段叫做 tota1，计算这本书的销售总额。
        而如果使用的是 aggregate，那么将求所有图书的销售总额。
        简单来说 简单聚合用 aggregate  分组聚合用 annotate
    :param request:
    :return:
    """
    # 分组聚合
    # SELECT `front_book`.`name`, SUM(`front_book_order`.`price`) AS `total` FROM `front_book` LEFT OUTER JOIN `front_book_order` ON (`front_book`.`id` = `front_book_order`.`book_id`) GROUP BY `front_book`.`id` ORDER BY NULL LIMIT 21
    query_set = Book.objects.annotate(price_total=Sum('bookorder__price')).values('name', 'price_total')
    print(query_set.query)
    result_all = query_set.all()
    if result_all:
        for item in result_all:
            print(item)
    return HttpResponse("sum_view")


# f表达式
def f_view(request):
    # 价格减去10
    # UPDATE `front_book` SET `price` = (`front_book`.`price` - 10);
    # UPDATE `front_book` SET `price` = (`front_book`.`price` - 10) WHERE `front_book`.`id` <= 2;
    Book.objects.filter(id__lte=2).update(price=F('price') - 10)
    return HttpResponse("f_view")


# q表达式 (条件是or)
def q_view(request):
    # 查找价格>=4000或者评分>=5的书籍
    # SELECT `front_book`.`name`, `front_book`.`price`, `front_book`.`rating` FROM `front_book` WHERE (`front_book`.`price` >= 4000.0e0 OR `front_book`.`rating` >= 5.0e0);
    query_set = Book.objects.filter(Q(price__gte=4000) | Q(rating__gte=5)).values('name', 'price', 'rating')
    set_all = query_set.all()
    if set_all:
        for item in set_all:
            print(item)
    return HttpResponse("q_view")


# 1.GET:用来从服务器上获取数据的
# 2.POST:用来向服务器提交数据
@require_http_methods(['POST', 'GET'])
def index(request):
    # 如果用GET请求，那么就直接返回一个页面
    if request.method == "GET":
        form = MessageBoardForm()
        context = {
            'form': form,
        }
        return render(request, "front/index.html", context)
    else:
        form = MessageBoardForm(request.POST)
        if form.is_valid():
            title = form.cleaned_data.get('title')
            content = form.cleaned_data.get('content')
            email = form.cleaned_data.get('email')
            return HttpResponse(f"title: {title} ,content: {content}, email: {email}")
        else:
            print(form.errors)
            return HttpResponse("表单验证失败")


@require_http_methods(['POST', 'GET'])
def register_view(request):
    if request.method == "GET":
        form = RegisterForm()
        context = {
            'form': form,
        }
        return render(request, 'front/register.html',context)
    else:
        form = RegisterForm(request.POST)
        if form.is_valid():
            tel = form.cleaned_data.get('tel')
            username = form.cleaned_data.get('username')
            password = form.cleaned_data.get('password')
            return HttpResponse(f"用户名 ： {username}  电话: {tel} 密码： {password}")
        else:
            print(form.errors.get_json_data())
            return HttpResponse("验证失败")


@require_http_methods(['POST', 'GET'])
def article_view(request):
    if request.method == "GET":
        return render(request, 'front/article.html')
    else:
        form = ArticleForm(request.POST)
        if form.is_valid():
            title = form.cleaned_data.get('title')
            content = form.cleaned_data.get('content')
            # 获敢title和 content以create_time, 然后创article模型对象，再存储到数据库中
            Article.objects.create(title=title, content=content).save()
            return HttpResponse(f"--{title}--{content}--数据保存 成功")
        else:
            print(form.errors.get_json_data())
            return HttpResponse(f"数据操作失败：{form.errors.get_json_data()}")

